using System;
using System.Collections.Generic;
using System.Text;

using System.Data;
using System.Data.OleDb;
using System.Collections;
using DTO;

namespace DAO
{
    public class TuaSachDao
    {
        #region Methods
        public DataSet LayBang()
        {
            //B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            OleDbConnection cn = DataProvider.ConnectionData();
            //B3: Tao chuoi strSQL thao tac CSDL
            string strSQL = "Select * From TUA_SACH";
            //B4: Thuc thi chuoi strSQL
            OleDbDataAdapter da = new OleDbDataAdapter(strSQL, cn);
            DataSet ds = new DataSet();
            da.FillSchema(ds, SchemaType.Source);   // can thiet
            da.Fill(ds);
            //B5: Dong ket noi CSDL
            cn.Close();
            return ds;
        }



        public IList LayDanhSach()
        {
            //B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            OleDbConnection cn = DataProvider.ConnectionData();
            //B3: Tao chuoi strSQL thao tac CSDL
            string strSQL = "Select * From TUA_SACH";
            //B4: Thuc thi chuoi strSQL
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            OleDbDataReader dr = cmd.ExecuteReader();
            TuaSachDto ts = null;
            ArrayList ds = new ArrayList();
            while (dr.Read())
            {
                ts = new TuaSachDto();
                ts.MaTuaSach = (int)dr["MaTuaSach"];
                ts.TenTuaSach = (string)dr["TenTuaSach"];
                ts.MaLoai = (int)dr["MaLoai"];
                ts.TacGia = (string)dr["TacGia"];
                ts.TomTat = (string)dr["TomTat"];

                ds.Add(ts);
            }

            // B5: Dong ket noi CSDL
            dr.Close();
            cn.Close();
            return ds;
        }



        public void CapNhatBang(DataSet ds)
        {
            //B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            OleDbConnection cn = DataProvider.ConnectionData();
            //B3: Tao chuoi strSQL thao tac CSDL
            string strSQL = "Select * From TUA_SACH";
            //B4: Thuc thi chuoi strSQL
            OleDbDataAdapter da = new OleDbDataAdapter(strSQL, cn);
            OleDbCommandBuilder cb = new OleDbCommandBuilder(da);  // khi cap nhat bang phai co dong lenh nay
            da.Update(ds.Tables[0]);
            //B5: Dong ket noi CSDL
            cn.Close();
        }



        public TuaSachDto TimKiem(int maTuaSach)
        {
            //B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            OleDbConnection cn = DataProvider.ConnectionData();
            //B3: Tao chuoi strSQL thao tac CSDL
            string strSQL = "Select * From TUA_SACH Where MaTuaSach = ?";
            //B4: Thuc thi chuoi strSQL
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@MaTuaSach", OleDbType.Integer);
            cmd.Parameters[0].Value = maTuaSach;
            OleDbDataReader dr = cmd.ExecuteReader();
            TuaSachDto ts = null;
            while (dr.Read())
            {
                ts = new TuaSachDto();
                ts.MaTuaSach = (int)dr["MaTuaSach"];
                ts.TenTuaSach = (string)dr["TenTuaSach"];
                ts.MaLoai = (int)dr["MaLoai"];
                ts.TacGia = (string)dr["TacGia"];
                ts.TomTat = (string)dr["TomTat"];
            }

            //B5: Dong ket noi CSDL
            cn.Close();
            return ts;
        }



        public void Them(TuaSachDto ts)
        {
            //B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            OleDbConnection cn = DataProvider.ConnectionData();
            //B3: Tao chuoi strSQL thao tac CSDL
            string strSQL = "Insert into TUA_SACH(TenTuaSach, MaLoai, TacGia, TomTat) values (?, ?, ?, ?)";
            //B4: Thuc thi chuoi strSQL
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@TenTuaSach", OleDbType.WChar);
            cmd.Parameters.Add("@MaLoai", OleDbType.Integer);
            cmd.Parameters.Add("@TacGia", OleDbType.WChar);
            cmd.Parameters.Add("@TomTat", OleDbType.WChar);

            cmd.Parameters["@TenTuaSach"].Value = ts.TenTuaSach;
            cmd.Parameters["@MaLoai"].Value = ts.MaLoai;
            cmd.Parameters["@TacGia"].Value = ts.TacGia;
            cmd.Parameters["@TomTat"].Value = ts.TomTat;
            cmd.ExecuteNonQuery();

            strSQL = "Select @@IDENTITY";
            cmd = new OleDbCommand(strSQL, cn);
            ts.MaTuaSach = (int)cmd.ExecuteScalar();
            //B5: Dong ket noi CSDL
            cn.Close();
        }




        public void Xoa(int maTuaSach)
        {
            //B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            OleDbConnection cn = DataProvider.ConnectionData();
            //B3: Tao chuoi strSQL thao tac CSDL
            string strSQL = "Delete From TUA_SACH Where MaTuaSach = ? ";
            //B4: Thuc thi chuoi strSQL
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@MaTuaSach", OleDbType.Integer);
            cmd.Parameters["@MaTuaSach"].Value = maTuaSach;
            cmd.ExecuteNonQuery();
            //B5: Dong ket noi CSDL
            cn.Close();
        }




        public void Sua(TuaSachDto ts)
        {
            //B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            OleDbConnection cn = DataProvider.ConnectionData();
            //B3: Tao chuoi strSQL thao tac CSDL
            string strSQL = "Update TUA_SACH set TenTuaSach = ?, MaLoai = ?, TacGia = ?, TomTat = ? where MaTuaSach = ?";

            //B4: Thuc thi chuoi strSQL
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            // thu tu rat quan trong
            cmd.Parameters.Add("@TenTuaSach", OleDbType.WChar);
            cmd.Parameters.Add("@MaLoai", OleDbType.Integer);
            cmd.Parameters.Add("@TacGia", OleDbType.WChar);
            cmd.Parameters.Add("@TomTat", OleDbType.WChar);
            cmd.Parameters.Add("@MaTuaSach", OleDbType.Integer);

            cmd.Parameters["@TenTuaSach"].Value = ts.TenTuaSach;
            cmd.Parameters["@MaLoai"].Value = ts.MaLoai;
            cmd.Parameters["@TacGia"].Value = ts.TacGia;
            cmd.Parameters["@TomTat"].Value = ts.TomTat;
            cmd.Parameters["@MaTuaSach"].Value = ts.MaTuaSach;
            cmd.ExecuteNonQuery();
            //B5: Dong ket noi CSDL
            cn.Close();
        }

        public int TimMaTuaSach(TuaSachDto tsCrt)
        {
            //B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            OleDbConnection cn = DataProvider.ConnectionData();
            //B3: Tao chuoi strSQL thao tac CSDL
            string strSQL = "Select MaTuaSach From TUA_SACH Where TenTuaSach = ? and MaLoai = ? and TacGia = ?";
            //B4: Thuc thi chuoi strSQL
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@TenTuaSach", OleDbType.WChar);
            cmd.Parameters.Add("@MaLoai", OleDbType.Integer);
            cmd.Parameters.Add("@TacGia", OleDbType.WChar);
     
            cmd.Parameters["@TenTuaSach"].Value = tsCrt.TenTuaSach;
            cmd.Parameters["@MaLoai"].Value = tsCrt.MaLoai;
            cmd.Parameters["@TacGia"].Value = tsCrt.TacGia;
           
            OleDbDataReader dr = cmd.ExecuteReader();
            int MaTuaSach = -1;
            while (dr.Read())
            {
                MaTuaSach = (int)dr["MaTuaSach"];
            }

            //B5: Dong ket noi CSDL
            cn.Close();
            return MaTuaSach;
        }

        public int KiemTraTuaSach(string tenTuaSach)
        {
            //B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            OleDbConnection cn = DataProvider.ConnectionData();
            //B3: Tao chuoi strSQL thao tac CSDL
            string strSQL = "Select * From TUA_SACH Where TenTuaSach = ?";
            //B4: Thuc thi chuoi strSQL
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@MaTuaSach", OleDbType.WChar);
            cmd.Parameters[0].Value = tenTuaSach;
            OleDbDataReader dr = cmd.ExecuteReader();
            int MaTuaSach = new int();
            MaTuaSach = -1;
            while (dr.Read())
            {
                MaTuaSach = (int)dr["MaTuaSach"];
            }

            //B5: Dong ket noi CSDL
            cn.Close();

           
                return MaTuaSach;
        }
        #endregion Methods

    }
}
